from datetime import datetime
from typing import List, Tuple
from fastapi import HTTPException, UploadFile
from tortoise.contrib.pydantic import pydantic_model_creator
from tortoise.queryset import QuerySet

from model.sysDeptModel import SysDept
from schema.sysDeptSchema import SysDeptCreate, SysDeptUpdate, PageParams
from io import BytesIO
from excel.sysDeptExcel import SysDeptExcel
from tortoise import connections
from model.sysUserModel import SysUser


class PydanticConfig:
    alias_generator = lambda x: ''.join(word.capitalize() if i else word for i, word in enumerate(x.split('_')))
    allow_population_by_field_name = True


SysDept_Pydantic = pydantic_model_creator(SysDept, name="SysDept", config_class=PydanticConfig)

DATA_SCOPE = {
    # 全部数据权限
    "DATA_SCOPE_ALL": "1",
    # 自定数据权限
    "DATA_SCOPE_CUSTOM": "2",
    # 部门数据权限
    "DATA_SCOPE_DEPT": "3",
    # 部门及以下数据权限
    "DATA_SCOPE_DEPT_AND_CHILD": "4",
    # 仅本人数据权限
    "DATA_SCOPE_SELF": "5"
}


class SysDeptService:
    def __init__(self):
        self.model = SysDept
        self.excel = SysDeptExcel
        self.conn = connections

    @staticmethod
    async def create(data: SysDeptCreate, creator: str) -> SysDept:
        """Create new record"""
        data_dict = data.dict(exclude_unset=True)
        data_dict["create_by"] = creator
        data_dict["create_time"] = datetime.now()
        data_dict["status"] = '0'

        obj = await SysDept.create(**data_dict)
        return await SysDept_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def get_by_id(id: int) -> SysDept:
        """Get record by ID"""
        obj = await SysDept.get_or_none(dept_id=id, status='0')
        if not obj:
            raise HTTPException(status_code=404, detail="部门表不存在")
        return await SysDept_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def update(id: int, data: SysDeptUpdate, updater: str) -> SysDept:
        """Update record"""
        obj = await SysDept.get_or_none(dept_id=id, status='0')
        if not obj:
            raise HTTPException(status_code=404, detail="部门表不存在")

        update_dict = data.dict(exclude_unset=True)
        update_dict["update_by"] = updater
        update_dict["update_time"] = datetime.now()

        await obj.update_from_dict(update_dict).save()
        return await SysDept_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def delete(ids: List[int], updater: str) -> int:
        """Batch delete records"""
        update_data = {
            "status": '2',
            "update_by": updater,
            "update_time": datetime.now()
        }
        count = await SysDept.filter(dept_id__in=ids, status='0').update(**update_data)
        return count

    @staticmethod
    async def get_page(params: PageParams) -> Tuple[List[SysDept], int]:
        """Get page list"""
        query = SysDept.filter(status='0')

        # Build query conditions

        if params.ancestors:
            query = query.filter(ancestors__icontains=params.ancestors)

        if params.dept_name:
            query = query.filter(dept_name__icontains=params.dept_name)

        if params.leader:
            query = query.filter(leader__icontains=params.leader)

        if params.phone:
            query = query.filter(phone__icontains=params.phone)

        if params.email:
            query = query.filter(email__icontains=params.email)

        if params.status:
            query = query.filter(status__icontains=params.status)

        if params.use_flag:
            query = query.filter(use_flag__icontains=params.use_flag)

        if params.create_by:
            query = query.filter(create_by__icontains=params.create_by)

        if params.update_by:
            query = query.filter(update_by__icontains=params.update_by)

        total = await query.count()

        records = await query.offset((params.page_num - 1) * params.page_size).limit(params.page_size).order_by(
            "-create_time")

        return [await SysDept_Pydantic.from_tortoise_orm(record) for record in records], total

    @staticmethod
    async def export_excel() -> BytesIO:
        """Export all data to Excel"""
        records = await SysDept.filter(status='0').all()
        return await SysDeptExcel.export_data(records)

    @staticmethod
    def get_import_template() -> BytesIO:
        """Get import template"""
        return SysDeptExcel.get_import_template()

    @staticmethod
    async def import_data(file: UploadFile, creator: str) -> Tuple[int, List[str]]:
        """Import data"""
        content = await file.read()
        file_bytes = BytesIO(content)

        try:
            data_list = SysDeptExcel.parse_import_data(file_bytes)
        except ValueError as e:
            raise HTTPException(status_code=400, detail=str(e))
        except Exception as e:
            raise HTTPException(status_code=400, detail="Excel文件格式错误")

        success_count = 0
        error_msgs = []

        for index, item_data in enumerate(data_list, start=1):
            try:
                item_data['create_by'] = creator
                item_data['create_time'] = datetime.now()
                item_data['status'] = '0'

                await SysDept.create(**item_data)
                success_count += 1

            except Exception as e:
                error_msgs.append(f"第{index}行: {str(e)}")

        return success_count, error_msgs

    @staticmethod
    async def get_all_list():
        data_list = await SysDept.filter(status='0').all()
        return [await SysDept_Pydantic.from_tortoise_orm(record) for record in data_list]

    @staticmethod
    async def get_exclude_list(dept_id: int):
        query = SysDept.filter(status='0').exclude(dept_id=dept_id)
        data_list = await query.all()
        return [await SysDept_Pydantic.from_tortoise_orm(record) for record in data_list]

    async def get_dept_tree(self, user_id: int):
        """
        获取部门树形结构
        返回按照orderNum排序的树形部门列表
        """
        # 1. 获取所有未删除且可用的部门数据
        query = await self.dept_scope_where(user_id)
        dept_list = await query.order_by('order_num').all()

        # 将查询结果转换为Pydantic模型
        dept_list = [await SysDept_Pydantic.from_tortoise_orm(dept) for dept in dept_list]

        # 2. 定义构建树形结构的内部函数
        def build_dept_tree(dept_all, parent_id=0):
            tree = []

            for dept in dept_all:
                if dept.parent_id == parent_id:
                    # 创建节点数据
                    node = {
                        "id": dept.dept_id,
                        "label": dept.dept_name,
                        "orderNum": dept.order_num,
                        "parent": dept.parent_id,
                        "leader": dept.leader,
                        "phone": dept.phone,
                        "email": dept.email,
                        "status": dept.status
                    }

                    # 递归获取子节点
                    children = build_dept_tree(dept_all, dept.dept_id)

                    # 只在有子节点时添加children属性
                    if children:
                        node["children"] = children

                    tree.append(node)

            # 按照orderNum排序
            return sorted(tree, key=lambda x: x["orderNum"])

        # 3. 构建并返回树形结构
        try:
            dept_tree = build_dept_tree(dept_list)
            return dept_tree
        except Exception as e:
            raise HTTPException(
                status_code=200,
                detail=f"构建部门树失败: {str(e)}"
            )

    async def find_role_by_user_id(self, user_id: int):
        """获取用户的角色列表"""
        query = """
                SELECT DISTINCT r.*
                FROM sys_role r
                INNER JOIN sys_user_role ur on ur.role_id = r.role_id
                WHERE ur.user_id = :userId;
            """

        conn = self.conn.get('default')
        results = await conn.execute_query_dict(query, [user_id])

        return results

    async def find_dept_by_user_id(self, user_id: int):
        """获取用户的角色列表"""
        query = """
                SELECT DISTINCT rd.*
                FROM sys_role_dept rd
                INNER JOIN sys_user_role ur on ur.role_id = rd.role_id
                WHERE ur.user_id = :userId;
            """

        conn = self.conn.get('default')
        results = await conn.execute_query_dict(query, [user_id])
        return results

    async def dept_scope_where(self, user_id: int) -> QuerySet:
        query = SysDept.filter(
            status='0',
            use_flag='0'
        )

        # 超级管理员查所有
        if user_id == 1:
            return query

        # 所有数据
        role_list = await self.find_role_by_user_id(user_id)
        has_all_value = any(item.data_scope == DATA_SCOPE.DATA_SCOPE_ALL for item in role_list)
        if has_all_value:
            return query

        # 自定义部门数据
        has_cust_value = any(item.data_scope == DATA_SCOPE.DATA_SCOPE_CUSTOM for item in role_list)
        if has_cust_value:
            dept_list = await self.find_dept_by_user_id(user_id)
            dept_id_list = [result['role_id'] for result in dept_list]
            query = query.filter(dept_id__in=dept_id_list)
            return query

        # 本部门数据
        has_dept_value = any(item.data_scope == DATA_SCOPE.DATA_SCOPE_DEPT for item in role_list)
        if has_dept_value:
            user_obj = await SysUser.get_or_none(user_id=user_id, status='0')
            if not user_obj:
                raise HTTPException(status_code=200, detail="用户不存在")

            dept_id = user_obj.dept_id
            query = query.filter(dept_id=dept_id)
            return query

        query = query.filter(dept_id=0)
        return query
